/*==============================================================*/
/* DBMS name:      ORACLE Version 10g                           */
/* Created on:     11.06.2011 17:48:14                          */
/*==============================================================*/





drop trigger CHECK_FOR_MAX_ENTRANTS
/

drop trigger AUTOINCREMENT_TRIG_ENTRANT
/

drop trigger CHECK_FOR_MAX_ENTRANTS_GROUP
/

drop trigger AUTOINCREMENT_TRIG_EXAM
/

drop trigger CHECK_FOR_MAXPOINTS
/

drop trigger AUTOINCREMENT_TRIG_GROUPA
/

drop trigger AUTOINCREMENT_TRIG_SPECIALITY
/

alter table APPLICATION
   drop constraint FK_APPLICATION_ENTRANT
/

alter table APPLICATION
   drop constraint FK_APPLICATION_SPECIALITY
/

alter table ENTRANT
   drop constraint FK_ENTRANT_GROUPA
/

alter table EXAM
   drop constraint FK_EXAM_SPECIALITY
/

alter table EXAM_RESULT
   drop constraint FK_EXAMRES_EXAM
/

alter table EXAM_RESULT
   drop constraint FK_EXAMRES_ENTRANT
/

alter table GROUPA
   drop constraint FK_GROUPA_SPECIALITY
/

drop index APPLICATION2_FK
/

drop index APPLICATION_FK
/

drop table APPLICATION cascade constraints
/

drop index GROUPA_ENTRANT_FK
/

drop table ENTRANT cascade constraints
/

drop index SPECIALITY_EXAMS_FK
/

drop table EXAM cascade constraints
/

drop index EXAM_RESULT2_FK
/

drop index EXAM_RESULT_FK
/

drop table EXAM_RESULT cascade constraints
/

drop index SPECIALITY_GROUP_FK
/

drop table GROUPA cascade constraints
/

drop table SPECIALITY cascade constraints
/

drop sequence S_ENTRANT
/

drop sequence S_EXAM
/

drop sequence S_GROUPA
/

drop sequence S_SPECIALITY
/

create sequence S_ENTRANT
increment by 1
start with 0
 minvalue 0
nocycle
 nocache
/

create sequence S_EXAM
increment by 1
start with 0
 minvalue 0
nocycle
 nocache
/

create sequence S_GROUPA
increment by 1
start with 0
 minvalue 0
nocycle
 nocache
/

create sequence S_SPECIALITY
increment by 1
start with 0
 minvalue 0
nocycle
 nocache
/

/*==============================================================*/
/* Table: SPECIALITY                                            */
/*==============================================================*/
create table SPECIALITY  (
   ID_SPECIALITY        NUMBER(6)                       not null,
   NAME                 VARCHAR2(50)                    not null,
   CODE                 VARCHAR2(15 CHAR)               not null,
   MAX_STUDENTS_NUMBER  NUMBER(6)                       not null,
   constraint PK_SPECIALITY primary key (ID_SPECIALITY)
)
/

/*==============================================================*/
/* Table: GROUPA                                                */
/*==============================================================*/
create table GROUPA  (
   ID_GROUP             NUMBER(6)                       not null,
   ID_SPECIALITY        NUMBER(6),
   MAX_STUDENTS_NUMBER  NUMBER(6)                       not null,
   CODE                 VARCHAR2(10 CHAR)               not null,
   constraint PK_GROUPA primary key (ID_GROUP),
   constraint FK_GROUPA_SPECIALITY foreign key (ID_SPECIALITY)
         references SPECIALITY (ID_SPECIALITY)
         on delete set null
)
/

/*==============================================================*/
/* Table: ENTRANT                                               */
/*==============================================================*/
create table ENTRANT  (
   ID_ENTRANT           NUMBER(6)                       not null,
   ID_GROUP             NUMBER(6),
   NAME                 VARCHAR2(50)                    not null,
   SURNAME              VARCHAR2(50)                    not null,
   ADDRESS              VARCHAR2(100),
   constraint PK_ENTRANT primary key (ID_ENTRANT),
   constraint FK_ENTRANT_GROUPA foreign key (ID_GROUP)
         references GROUPA (ID_GROUP)
         on delete set null
)
/

/*==============================================================*/
/* Table: APPLICATION                                           */
/*==============================================================*/
create table APPLICATION  (
   ID_ENTRANT           NUMBER(6)                       not null,
   ID_SPECIALITY        NUMBER(6)                       not null,
   ACCEPTED             NUMBER(6),
   constraint PK_APPLICATION primary key (ID_ENTRANT, ID_SPECIALITY),
   constraint FK_APPLICATION_ENTRANT foreign key (ID_ENTRANT)
         references ENTRANT (ID_ENTRANT)
         on delete cascade,
   constraint FK_APPLICATION_SPECIALITY foreign key (ID_SPECIALITY)
         references SPECIALITY (ID_SPECIALITY)
         on delete cascade
)
/

/*==============================================================*/
/* Index: APPLICATION_FK                                        */
/*==============================================================*/
create index APPLICATION_FK on APPLICATION (
   ID_ENTRANT ASC
)
/

/*==============================================================*/
/* Index: APPLICATION2_FK                                       */
/*==============================================================*/
create index APPLICATION2_FK on APPLICATION (
   ID_SPECIALITY ASC
)
/

/*==============================================================*/
/* Index: GROUPA_ENTRANT_FK                                     */
/*==============================================================*/
create index GROUPA_ENTRANT_FK on ENTRANT (
   ID_GROUP ASC
)
/

/*==============================================================*/
/* Table: EXAM                                                  */
/*==============================================================*/
create table EXAM  (
   ID_EXAM              NUMBER(6)                       not null,
   ID_SPECIALITY        NUMBER(6),
   SUBJECT              VARCHAR2(50)                    not null,
   TYPE                 VARCHAR2(20)                    not null,
   MAX_POINTS           NUMBER(6)                      default 0,
   DATE_TIME            DATE                            not null,
   ADDRESS              VARCHAR2(100)                   not null,
   constraint PK_EXAM primary key (ID_EXAM),
   constraint FK_EXAM_SPECIALITY foreign key (ID_SPECIALITY)
         references SPECIALITY (ID_SPECIALITY)
         on delete set null
)
/

/*==============================================================*/
/* Index: SPECIALITY_EXAMS_FK                                   */
/*==============================================================*/
create index SPECIALITY_EXAMS_FK on EXAM (
   ID_SPECIALITY ASC
)
/

/*==============================================================*/
/* Table: EXAM_RESULT                                           */
/*==============================================================*/
create table EXAM_RESULT  (
   ID_EXAM              NUMBER(6)                       not null,
   ID_ENTRANT           NUMBER(6)                       not null,
   POINTS               NUMBER(6),
   constraint PK_EXAM_RESULT primary key (ID_EXAM, ID_ENTRANT),
   constraint FK_EXAMRES_EXAM foreign key (ID_EXAM)
         references EXAM (ID_EXAM)
         on delete cascade,
   constraint FK_EXAMRES_ENTRANT foreign key (ID_ENTRANT)
         references ENTRANT (ID_ENTRANT)
         on delete cascade
)
/

/*==============================================================*/
/* Index: EXAM_RESULT_FK                                        */
/*==============================================================*/
create index EXAM_RESULT_FK on EXAM_RESULT (
   ID_EXAM ASC
)
/

/*==============================================================*/
/* Index: EXAM_RESULT2_FK                                       */
/*==============================================================*/
create index EXAM_RESULT2_FK on EXAM_RESULT (
   ID_ENTRANT ASC
)
/

/*==============================================================*/
/* Index: SPECIALITY_GROUP_FK                                   */
/*==============================================================*/
create index SPECIALITY_GROUP_FK on GROUPA (
   ID_SPECIALITY ASC
)
/


create or replace trigger CHECK_FOR_MAX_ENTRANTS
before insert on application
for each row
declare
    entrants_count number;
    max_entrants number;
begin
    select count(*) into entrants_count from application 
    where id_speciality = :new.id_speciality;
    
    select max_students_number into max_entrants from speciality s
    where s.id_speciality = :new.id_speciality;
    
    if entrants_count >= max_entrants then
        raise_application_error(-20001, 'Max number of entrants reached to this speciality');
    end if;
end;
/


create trigger AUTOINCREMENT_TRIG_ENTRANT before insert
on ENTRANT for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

begin
    --  Column "ID_ENTRANT" uses sequence S_ENTRANT
    select S_ENTRANT.NEXTVAL INTO :new.ID_ENTRANT from dual;

--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create or replace trigger CHECK_FOR_MAX_ENTRANTS_GROUP
before insert or update on entrant 
for each row
when(new.id_group is not null)
declare
    entrants_count number;
    max_entrants number;
begin
    select count(*) into entrants_count from entrant 
    where id_group = :new.id_group;
    
    select max_students_number into max_entrants from groupa g
    where g.id_group = :new.id_group;
    
    if entrants_count >= max_entrants then
        raise_application_error(-20002, 'Max number of entrants reached for this group');
    end if;
end;
/


create trigger AUTOINCREMENT_TRIG_EXAM before insert
on EXAM for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

begin
    --  Column "ID_EXAM" uses sequence S_EXAM
    select S_EXAM.NEXTVAL INTO :new.ID_EXAM from dual;

--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create or replace trigger CHECK_FOR_MAXPOINTS
before insert or update on exam_result
for each row
when(new.points is not null)
declare
    maxpoints number;
begin
    select max_points into maxpoints from exam
    where id_exam = :new.id_exam;
    
    if maxpoints is not null then
        if :new.points > maxpoints then
            :new.points = maxpoints;
        end if;
    end if;
end;
/


create trigger AUTOINCREMENT_TRIG_GROUPA before insert
on GROUPA for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

begin
    --  Column "ID_GROUP" uses sequence S_GROUPA
    select S_GROUPA.NEXTVAL INTO :new.ID_GROUP from dual;

--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create trigger AUTOINCREMENT_TRIG_SPECIALITY before insert
on SPECIALITY for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

begin
    --  Column "ID_SPECIALITY" uses sequence S_SPECIALITY
    select S_SPECIALITY.NEXTVAL INTO :new.ID_SPECIALITY from dual;

--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/

